<?php

class CandidatesController extends AppController {

    var $name = 'Candidates';

    function index() {
        $id = $this->Session->read('proy_id');
        if ($id == "") {
            $this->Session->setFlash('No ha seleccionado Proyecto');
            $this->redirect(array('controller' => 'Pages', 'action' => 'display'));
        } else {
            $this->Candidate->recursive = -1;
            $ben = $this->Candidate->find('all', array('conditions' => array('Candidate.proyect_id' => $id, 'Candidate.candidate_id' => 0), 'fields' => array('Candidate.id', 'Candidate.1er_nombre', 'Candidate.1er_apellido', 'Candidate.2do_apellido', 'Candidate.nro_ident', 'Candidate.tipo')));
            $this->set("candidates", $ben);
            $this->set('cod', $this->Session->read('cod'));
            $this->set('id', $id);
        }
    }

    function add() {
        $this->set('est', $this->Session->read('estado'));
        $this->layout = "ajax";
        $id = $this->Session->read('proy_id');

        $this->set('id', $id);
        $this->set('cod', $this->Session->read('cod'));


        if (!empty($this->data)) {

            if ($this->Candidate->saveAll($this->data)) {
                $this->Session->setFlash('Aspirante guardado con exito');
                $this->redirect(array('controller' => 'Candidates', 'action' => 'index'));
            } else {
                $this->Session->setFlash('error guardando datos');
            }
        } else {
            
        }
    }

    function edit($id = null) {
        $this->set('cod', $this->Session->read('cod'));
        $pro_id = $this->Session->read('proy_id');
        $estado = $this->Session->read('proy_id');
        $this->set('est', $this->Session->read('estado'));
        $this->Candidate->recursive = -1;
        $this->set('grupo', $this->Auth->user('group_id'));

        if (empty($this->data)) {
            $this->data = $this->Candidate->find("first", array("conditions" => array("Candidate.id" => $id)));
        } else {
            if ($this->Candidate->save($this->data)) {
                $this->Session->setFlash('Aspirante Actualizado con exito');
                $this->redirect(array('controller' => 'Candidates', 'action' => 'index'));
            } else {
                $this->Session->setFlash('Error guardando datos');
            }
        }
    }

    function filtro_index() {

        $id = $this->Session->read('proy_id');


        if ($id == "") {
            $this->Session->setFlash('No ha seleccionado Proyecto');
            $this->redirect(array('controller' => 'Proyects', 'action' => 'index'));
        } else {
            $this->Candidate->recursive = -1;
            $titulares = $this->Candidate->find('all', array('conditions' => array('Candidate.proyect_id' => $id, 'Candidate.candidate_id ' => 0), 'fields' => array('Candidate.id', 'Candidate.2do_nombre', 'Candidate.1er_nombre', 'Candidate.1er_apellido', 'Candidate.2do_apellido', 'Candidate.nro_ident', 'Candidate.tipo')));
            $conyugues = $this->Candidate->find('all', array('conditions' => array('Candidate.proyect_id' => $id, 'Candidate.candidate_id !=' => 0), 'fields' => array('Candidate.id', 'Candidate.2do_nombre', 'Candidate.1er_nombre', 'Candidate.1er_apellido', 'Candidate.2do_apellido', 'Candidate.nro_ident', 'Candidate.tipo', 'Candidate.candidate_id')));
            $this->set('titulares', $titulares);
            $this->set('conyugues', $conyugues);
            $this->set('cod', $this->Session->read('cod'));
        }
    }

    function getColor($param) {

        foreach ($param as $key => $value) {

            //Busqueda de beneficiarios con la misma cedula
            $duplicados = $this->Candidate->find('count', array('conditions' => array('nro_ident' => $value['Candidate']['nro_ident'])));

            if ($duplicados > 1) {
                $value['Candidate']['dup_color'] = "rgb(245, 169, 169)";
            } else {
                $value['Candidate']['dup_color'] = "";
            }

            $param[$key]['Candidate'] = $value['Candidate'];
        }
        return $param;
    }

    function add_conyugue($id_parent = null) {

        $this->layout = "ajax";
        $id = $this->Session->read('proy_id');
        $this->set('est', $this->Session->read('estado'));
        $this->set('id', $id);



        if (!empty($this->data)) {
            $tipo = $this->Candidate->find('first', array('conditions' => array('Candidate.id' => $this->data['Candidate']['candidate_id'])));
            if ($tipo['Candidate']['tipo'] == 2) {
                $tp = 6;
            } elseif ($tipo['Candidate']['tipo'] == 4) {
                $tp = 7;
            } elseif ($tipo['Candidate']['tipo'] == 5) {
                $tp = 8;
            }


            $this->data['Candidate']['tipo'] = $tp;


            if ($this->Candidate->saveAll($this->data)) {
                $this->Session->setFlash('Aspirante guardado con exito');
                $this->redirect(array('controller' => 'Candidates', 'action' => 'index'));
            } else {
                $this->Session->setFlash('Error guardando datos');
                $this->set('nombre', $this->Candidate->find('first', array('conditions' => array('Candidate.id' => $this->data['Candidate']['candidate_id']))));
            }
        } else {

            $this->data['Candidate']['candidate_id'] = $id_parent;
            $this->set('nombre', $this->Candidate->find('first', array('conditions' => array('Candidate.id' => $this->data['Candidate']['candidate_id']))));
        }
    }

    function delete_conyugue($id) {
        if ($this->Candidate->delete(array('Candidate.id' => $id), true)) {
            $this->Session->setFlash('Conyugue Borrado con éxito');
            $this->setAction('index');
        }
    }

    function delete($id) {
        //obtengo el id del conyuge
        $this->Candidate->recursive = -1;
        $conyuges = $this->Candidate->find('all', array('conditions' => array('Candidate.candidate_id' => $id), 'fields' => array('Candidate.id')));
        //borro los datos de todos los conyuges
        foreach ($conyuges as $value) {
            $conyuge = $value["Candidate"]["id"];
            //Se obtiene el array con los id de los candidate_requirements
            $candidate_requirements = $this->Candidate->query("SELECT id FROM candidate_requirements 
                WHERE candidate_id = $conyuge;");

            //se borrar los registros en la tabla candidate_requirements_requirement_faults
            foreach ($candidate_requirements as $value1) {

                $candidate_requirement = $value1["candidate_requirements"]["id"];
                $this->Candidate->query("DELETE FROM candidate_requirements_requirement_faults
WHERE candidate_requirement_id = $candidate_requirement ;");
            }
            //borro los registros de la tabla candidate_requirements
            $this->Candidate->query("DELETE FROM candidate_requirements
WHERE candidate_id =$conyuge ;");
            //borro el registro de la tabla candidates
            $this->Candidate->query("DELETE FROM candidates
WHERE id =$conyuge ;");
        }
        //borro los registros de la tabla relatives

        if ($this->Candidate->query("DELETE FROM relatives
WHERE candidate_id =$id ;")) {
            $this->Session->setFlash('Parientes borrrados con éxito');
        }

        //borro los datos del candidato

        $candidate_requirements = $this->Candidate->query("SELECT id FROM candidate_requirements 
                WHERE candidate_id = $id;");


        foreach ($candidate_requirements as $value1) {

            $candidate_requirement = $value1["candidate_requirements"]["id"];
            $this->Candidate->query("DELETE FROM candidate_requirements_requirement_faults
WHERE candidate_requirement_id = $candidate_requirement ;");
        }

        //borro los registros de la tabla candidate_requirements

        $this->Candidate->query("DELETE FROM candidate_requirements
WHERE candidate_id =$id ;");
        //borro el registro de la tabla candidates
        $this->Candidate->query("DELETE FROM candidates
WHERE id =$id ;");

        $this->redirect(array('controller' => 'Candidates', 'action' => 'index'));
    }

    function repetidos() {
        $id = $this->Session->read('proy_id');
        if ($id == "") {
            $this->Session->setFlash('No ha seleccionado Proyecto');
            $this->redirect(array('controller' => 'Proyects', 'action' => 'index'));
        } else {
            $this->Candidate->recursive = -1;
            $ben = $this->Candidate->query(
                    "SELECT candidato1.tipo, candidato1.`nro_ident` , candidato1.`1er_apellido` ,
                    candidato1.`1er_nombre`,  proyectoCandidato2.codigo,
                    candidato2.tipo, candidato2.`nro_ident` , candidato2.`1er_apellido` , candidato2.`1er_nombre`
                    FROM candidates candidato1
                    LEFT JOIN candidates candidato2 ON candidato1.`nro_ident` = candidato2.`nro_ident`
                    LEFT JOIN proyects proyectoCandidato2 ON candidato2.proyect_id = proyectoCandidato2.id
                    WHERE candidato1.id != candidato2.id AND candidato1.proyect_id = $id");

            $this->set('candidates', $ben);
            $this->set('cod', $this->Session->read('cod'));
        }
    }

    function candidatos_en_familiares() {
        $id = $this->Session->read('proy_id');
        if ($id == "") {
            $this->Session->setFlash('No ha seleccionado Proyecto');
            $this->redirect(array('controller' => 'Proyects', 'action' => 'index'));
        } else {
            $this->Candidate->recursive = -1;
            $ben = $this->Candidate->query(
                    "SELECT candidato.`nro_ident` , candidato.`1er_apellido` ,candidato.`1er_nombre`,
                     proyectoDelCandidatoDelFamiliar.codigo, candidatoDelFamiliar.nro_ident, familiar.`nro_ident` , familiar.`1er_apellido` , familiar.`nombres`
                     FROM candidates candidato
                     LEFT JOIN relatives familiar ON candidato.`nro_ident` = familiar.`nro_ident`
                     LEFT JOIN candidates candidatoDelFamiliar ON familiar.candidate_id = candidatoDelFamiliar.id
                     LEFT JOIN proyects proyectoDelCandidatoDelFamiliar ON candidatoDelFamiliar.proyect_id = proyectoDelCandidatoDelFamiliar.id
                     WHERE candidato.proyect_id = $id AND candidato.nro_ident = familiar.nro_ident");

            $this->set('candidates', $ben);
            $this->set('cod', $this->Session->read('cod'));
        }
    }

    function candidate_report() {
        $this->layout = 'csv';
        ini_set('max_execution_time', 600); //increase max_execution_time to 10 min if data set is very large
        //create a file
        date_default_timezone_set("America/Bogota");
        $filename = "export_" . date("Y.m.d") . ".csv";
        $csv_file = fopen('php://output', 'w');
        $dep = $this->Auth->user('branch_id');
        header('Content-type: application/csv');
        header('Content-Disposition: attachment; filename="' . $filename . '"');

        if ($this->Auth->user('group_id') != 1) {
            $sql = "SELECT codigo, `nro_ident` , `1er_apellido` , `1er_nombre` , texto
                  FROM `candidates` Candidate
                  LEFT JOIN candidate_requirements CandidateRequirement ON Candidate.id = CandidateRequirement.candidate_id
                  LEFT JOIN proyects Proyect ON Candidate.proyect_id = Proyect.id
                  LEFT JOIN requirements Requirement ON CandidateRequirement.requirement_id = Requirement.id
                  WHERE CandidateRequirement.calificacion =2 AND Proyect.branch_id=$dep
                  ORDER BY `Proyect`.`codigo` ASC";
        } else {
            $sql = "SELECT codigo, `nro_ident` , `1er_apellido` , `1er_nombre` , texto
                  FROM `candidates` Candidate
                  LEFT JOIN candidate_requirements CandidateRequirement ON Candidate.id = CandidateRequirement.candidate_id
                  LEFT JOIN proyects Proyect ON Candidate.proyect_id = Proyect.id
                  LEFT JOIN requirements Requirement ON CandidateRequirement.requirement_id = Requirement.id
                  WHERE CandidateRequirement.calificacion =2
                  ORDER BY `Proyect`.`codigo` ASC";
        }

        $results = $this->Candidate->query($sql);

        // The column headings of your .csv file
        $header_row = array("PROYECTO", "DOCUMENTO", "PRIMER APELLIDO", "PRIMER NOMBRE", "REQUISITO NO CUMPLIDO");
        fputcsv($csv_file, $header_row, ';', '"');

        // Each iteration of this while loop will be a row in your .csv file where each field corresponds to the heading of the column
        foreach ($results as $result) {
            // Array indexes correspond to the field names in your db table(s)
            $row = array(
                $result['Proyect']['codigo'],
                $result['Candidate']['nro_ident'],
                $result['Candidate']['1er_apellido'],
                $result['Candidate']['1er_nombre'],
                $result['Requirement']['texto']
            );

            fputcsv($csv_file, $row, ';', '"');
        }

        fclose($csv_file);
    }

    function sipod() {
        if (!empty($this->data)) {
            $ide = $this->data['Sipod']['cedula'];
            $resultados = $this->Candidate->query("SELECT TIPO_DOC,IDENTIFICACION,PRIMERAPELLIDO,SEGUNDOAPELLIDO,PRIMERNOMBRE,SEGUNDONOMBRE FROM sipod Sipod WHERE IDENTIFICACION LIKE('%$ide%')");
            $this->set('resultados', $resultados);
        }
    }

    function search() {
        if (!empty($this->data)) {
            $this->Candidate->recursive = 0;
            $ide = $this->data['Candidate']['documento'];
            if ($ide != "") {

                $resultados = $this->Candidate->find('all', array('conditions' => array(
                        "or" => array(
                            "Candidate.nro_ident LIKE" => "%$ide%",
                            "Candidate.1er_nombre LIKE" => "%$ide%",
                            "CONCAT_WS(' ', Candidate.1er_nombre,Candidate.1er_apellido) LIKE" => "%$ide%",
                            "CONCAT_WS(' ', Candidate.1er_nombre,Candidate.2do_nombre,Candidate.1er_apellido) LIKE" => "%$ide%",
                            "CONCAT_WS(' ', Candidate.1er_nombre,Candidate.1er_apellido,Candidate.2do_apellido) LIKE" => "%$ide%",
                            "CONCAT_WS(' ', Candidate.1er_nombre,Candidate.2do_nombre,Candidate.1er_apellido,2do_apellido) LIKE" => "%$ide%",
                            "Candidate.1er_apellido LIKE" => "%$ide%",
                            "Candidate.2do_apellido LIKE" => "%$ide%",
                            "Candidate.2do_nombre LIKE" => "%$ide%",
                        )
                    ), 'fields' => array('Proyect.codigo', 'Candidate.tipo_ident', 'Candidate.nro_ident', 'Candidate.1er_nombre', 'Candidate.1er_apellido', 'Candidate.2do_nombre', 'Candidate.2do_apellido')));
                $this->Candidate->Relative->recursive = 0;
                $familiares = $this->Candidate->Relative->find('all', array('conditions' => array(
                        "or" => array(
                            "Relative.nro_ident LIKE" => "%$ide%",
                            "Relative.nombres LIKE" => "%$ide%",
                            "CONCAT_WS(' ', Relative.nombres,Relative.1er_apellido) LIKE" => "%$ide%",
                            "CONCAT_WS(' ', Relative.nombres,Relative.1er_apellido,Relative.2do_apellido) LIKE" => "%$ide%",
                            "CONCAT_WS(' ', Relative.nombres,Relative.2do_apellido) LIKE" => "%$ide%",
                        )
                    ), 'fields' => array('Candidate.proyect_id', 'Relative.tipo_ident', 'Relative.nro_ident', 'Relative.nombres', 'Relative.1er_apellido', 'Relative.2do_apellido')));

                App::Import('Model', 'Owner');
                $owner = new Owner();
                $propietarios = $owner->find('all', array('conditions' => array(
                        "or" => array(
                            "Owner.documento LIKE" => "%$ide%",
                            "Owner.nombres LIKE" => "%$ide%",
                            "CONCAT_WS(' ',Owner.nombres,Owner.primer_apellido) LIKE" => "%$ide%",
                            "CONCAT_WS(' ',Owner.nombres,Owner.primer_apellido,Owner.segundo_apellido) LIKE" => "%$ide%",
                            "CONCAT_WS(' ',Owner.nombres,Owner.segundo_apellido) LIKE" => "%$ide%",
                        )
                        )));
                App::Import('Model', 'Proyect');
                $proyect = new Proyect();
                $proyect->Proposer->recursive = 0;
                $proponentes = $proyect->Proposer->find('all', array('conditions' => array(
                        "or" => array(
                            "Proposer.numero_identificacion LIKE" => "%$ide%",
                            "Proposer.nombre LIKE" => "%$ide%",
                            "Proposer.rep_cedula LIKE" => "%$ide%",
                            "CONCAT_WS(' ',Proposer.rep_nombres,Proposer.rep_apellidos) LIKE" => "%$ide%",
                        )
                    ), 'fields' => array('Proyect.codigo', 'Proposer.nombre', 'Proposer.numero_identificacion', 'Proposer.rep_nombres', 'Proposer.rep_apellidos', 'Proposer.rep_cedula')));

                $proyect->Property->recursive = -1;
                $predios = $proyect->Property->find('all', array('conditions' => array(
                        "or" => array(
                            "Property.nombre LIKE" => "%$ide%",
                            "Property.matricula LIKE" => "%$ide%",
                            "Property.nro_catastral LIKE" => "%$ide%",
                        )
                    ), 'fields' => array('Property.codigo', 'Property.nombre', 'Property.matricula', 'Property.nro_catastral', 'Property.vereda')));

                $igac = $proyect->Property->query("SELECT Igac.nombre,Igac.documento,Igac.divipol,Igac.numero_predio,Igac.area_terreno,Igac.area_construida,Igac.avaluo,Igac.municipio_id,Igac.departamento,Igac.matricula FROM igac Igac WHERE (Igac.documento LIKE '%$ide%' OR Igac.nombre LIKE '%$ide%') AND Igac.tipo <> 3");
                $this->set('igac', $igac);
                $this->set('predios', $predios);
                $this->set('proponentes', $proponentes);
                $this->set('resultados', $resultados);
                $this->set('familiares', $familiares);
                $this->set('propietarios', $propietarios);
            }
        }
    }

    function state() {
        $this->layout = "ajax";
        $id = $this->Session->read('proy_id');
        if ($id == "") {
            $this->Session->setFlash('No ha seleccionado Proyecto');
            $this->redirect(array('controller' => 'Pages', 'action' => 'display'));
        } else {
            $this->Candidate->recursive = -1;
            $ben = $this->Candidate->find('all', array('conditions' => array('Candidate.proyect_id' => $id), 'fields' => array('Candidate.id', 'Candidate.1er_nombre', 'Candidate.1er_apellido', 'Candidate.2do_apellido', 'Candidate.nro_ident', 'Candidate.tipo', 'Candidate.estado_filtro', 'Candidate.candidate_id'), 'order' => array('Candidate.estado_filtro DESC')));
            $this->set("candidates", $ben);
            $this->set('cod', $this->Session->read('cod'));
            $this->set('id', $id);
            $cupos = $this->Candidate->find('count', array('conditions' => array('Candidate.proyect_id' => $this->Session->read('proy_id'), 'Candidate.clase' => 1)));
            $suplentes = $this->Candidate->find('count', array('conditions' => array('Candidate.proyect_id' => $this->Session->read('proy_id'), 'Candidate.clase' => 2)));
            $activos = $this->Candidate->find('count', array('conditions' => array('Candidate.proyect_id' => $this->Session->read('proy_id'), 'Candidate.estado_filtro' => array(1, 5, 6), 'Candidate.candidate_id' => 0)));

            $this->set('cupos', $cupos);
            $this->set('suplentes', $suplentes);
            $this->set('activos', $activos);
        }
    }

    function index_quality_control() {
        $this->layout = "ajax";
        $id = $this->Session->read('proy_id');
        if ($id == "") {
            $this->Session->setFlash('No ha seleccionado Proyecto');
            $this->redirect(array('controller' => 'Proyects', 'action' => 'index'));
        } else {
            $this->Candidate->recursive = -1;
            $ben = $this->Candidate->find('all', array('conditions' => array('Candidate.proyect_id' => $id), 'fields' => array('Candidate.id', 'Candidate.1er_nombre', 'Candidate.1er_apellido', 'Candidate.2do_apellido', 'Candidate.nro_ident', 'Candidate.estado_filtro', 'Candidate.candidate_id'), 'order' => array('Candidate.estado_filtro DESC')));
            $this->set("candidates", $ben);
            $this->set('cod', $this->Session->read('cod'));
            $this->set('id', $id);
            $cupos = $this->Candidate->find('count', array('conditions' => array('Candidate.proyect_id' => $this->Session->read('proy_id'), 'Candidate.clase' => 1)));
            $suplentes = $this->Candidate->find('count', array('conditions' => array('Candidate.proyect_id' => $this->Session->read('proy_id'), 'Candidate.clase' => 2)));
            $activos = $this->Candidate->find('count', array('conditions' => array('Candidate.proyect_id' => $this->Session->read('proy_id'), 'Candidate.estado_filtro' => array(1, 5, 6), 'Candidate.candidate_id' => 0)));

            $this->set('cupos', $cupos);
            $this->set('suplentes', $suplentes);
            $this->set('activos', $activos);
        }
    }

    function causal($id) {
        $this->Candidate->recursive = 0;
        $this->layout = "ajax";

        $this->Candidate->CandidateRequirement->unbindModel(
                array('hasAndBelongsToMany' => array('CandidateRequirementRequirementFault'))
        );
        $requerimientos = $this->Candidate->CandidateRequirement->find('all', array('conditions' => array('Candidate.id' => $id, 'CandidateRequirement.calificacion <>' => 1), 'fields' => array('Candidate.1er_nombre', 'Candidate.1er_apellido', 'Candidate.nro_ident', 'CandidateRequirement.id', 'CandidateRequirement.calificacion', 'CandidateRequirement.cal_subsanacion', 'CandidateRequirement.con_subsanacion', 'Requirement.texto')));
        $conyuge = $this->Candidate->CandidateRequirement->find('all', array('conditions' => array('Candidate.candidate_id' => $id, 'CandidateRequirement.calificacion <>' => 1), 'fields' => array('Candidate.1er_nombre', 'Candidate.1er_apellido', 'Candidate.nro_ident', 'CandidateRequirement.id', 'CandidateRequirement.calificacion', 'Requirement.texto', 'CandidateRequirement.cal_subsanacion', 'CandidateRequirement.con_subsanacion')));
        $this->Candidate->recursive = -1;
        $candidato = $this->Candidate->find('first', array('conditions' => array('Candidate.id' => $id), 'fields' => array('Candidate.nro_ident', 'Candidate.id')));
        $pareja = $this->Candidate->find('first', array('conditions' => array('Candidate.candidate_id' => $candidato['Candidate']['id']), 'fields' => array('Candidate.nro_ident', 'Candidate.id')));
        $this->set('pareja', $pareja);
        $this->set('candidato', $candidato);
        $this->set('cabeza', $requerimientos);
        $this->set('conyuge', $conyuge);
    }

    function print_state() {
        $this->layout = 'pdf';
        $id = $this->Session->read('proy_id');
        if ($id == "") {
            $this->Session->setFlash('No ha seleccionado Proyecto');
            $this->redirect(array('controller' => 'Proyects', 'action' => 'index'));
        } else {
            $this->Candidate->recursive = -1;
            $ben = $this->Candidate->find('all', array('conditions' => array('Candidate.proyect_id' => $id, 'Candidate.candidate_id' => 0), 'fields' => array('Candidate.id', 'Candidate.1er_nombre', 'Candidate.1er_apellido', 'Candidate.2do_apellido', 'Candidate.nro_ident', 'Candidate.tipo', 'Candidate.estado_filtro'), 'order' => array("Candidate.estado_filtro" => "DESC", "Candidate.1er_apellido" => "ASC")));
            $this->set("candidates", $ben);
            $this->set('cod', $this->Session->read('cod'));
            $this->set('id', $id);
            $cupos = $this->Candidate->find('count', array('conditions' => array('Candidate.proyect_id' => $this->Session->read('proy_id'), 'Candidate.clase' => 1)));
            $suplentes = $this->Candidate->find('count', array('conditions' => array('Candidate.proyect_id' => $this->Session->read('proy_id'), 'Candidate.clase' => 2)));
            $activos = $this->Candidate->find('count', array('conditions' => array('Candidate.proyect_id' => $this->Session->read('proy_id'), 'Candidate.estado_filtro' => array(1, 5, 6), 'Candidate.candidate_id' => 0)));
            $this->set('cupos', $cupos);
            $this->set('suplentes', $suplentes);
            $this->set('activos', $activos);
            $this->set('cod', $this->Session->read('cod'));
        }
    }

    function select() {
        $this->set('cities', $this->Candidate->City->find('list', array(
                    'order' => 'name ASC',
                    'conditions' => array('City.departament_id' => $this->data['Candidate']['departament_id'])
                        )
                ));
    }

    function sider_report() {
        $this->layout = 'csv';
        $this->Candidate->recursive = -1;
        $this->Candidate->Proyect->recursive = -1;
        $Proyectos = $this->Candidate->Proyect->find('all', array('recursive' => -1, 'fields' => array('Proyect.id', 'Proyect.codigo'), 'conditions' => array('Proyect.call_id' => 1)));
        $csv_file = fopen('php://output', 'w');
        $filename = "Reporte_Aspirantes_" . date("Y.m.d") . ".csv";
        header('Content-type: application/csv');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        $cabecera = array("Convocatoria", "CodProyecto", "CodigoBeneficiario", "Tipo ID Cabeza Familia", "No. ID Cabeza Familia", "Tipo ID", "No. ID", "1er apellido", "2o apellido", "Nombre(s)", "Género", "Parentesco", "Tipo Poblacion", "CRUCE DE BASE DE DATOS", "SISBEN", "DAS", "ACCION_SOC", "INCORA");
        fputcsv($csv_file, $cabecera, ';', '"');
        foreach ($Proyectos as $Proyecto) {

            $aspirantes = $this->Candidate->find('all', array('recursive' => -1, 'conditions' => array('Candidate.candidate_id' => 0, 'Candidate.proyect_id' => $Proyecto['Proyect']['id']), 'fields' => array('Candidate.id', 'Candidate.candidate_id', "Candidate.tipo_ident", "Candidate.nro_ident", "Candidate.1er_apellido", "Candidate.2do_apellido", "Candidate.1er_nombre", "Candidate.genero", "Candidate.tipo", "Candidate.clase", "Candidate.duplicado", "Candidate.proyect_id", "Candidate.2do_nombre")));

            foreach ($aspirantes as $aspirante) {
                $row = array();
                $row[] = "SIT-2011-01";
                $row[] = $Proyecto['Proyect']['codigo'];

                $row[] = $aspirante['Candidate']['id'];
                $tipo_iden = "";
                switch ($aspirante['Candidate']['tipo_ident']) {
                    case 1:
                        $tipo_iden = "C.C";
                        break;
                    case 2:
                        $tipo_iden = "T.I";
                        break;

                    default:
                        $tipo_iden = "Sin registro";
                        break;
                }
                $row[] = $tipo_iden;
                $row[] = $aspirante['Candidate']['nro_ident'];
                //Se repite para que quede el registro del cabeza de familia
                $row[] = $tipo_iden;
                $row[] = $aspirante['Candidate']['nro_ident'];
                $row[] = $aspirante['Candidate']['1er_apellido'];
                $row[] = $aspirante['Candidate']['2do_apellido'];
                $row[] = $aspirante['Candidate']['1er_nombre'] . " " . $aspirante['Candidate']['2do_nombre'];
                $genero = "";

                switch ($aspirante['Candidate']['genero']) {
                    case 1:
                        $genero = "Hombre";
                        break;
                    case 2:
                        $genero = "Mujer";
                        break;

                    default:
                        $genero = "Sin registro";
                        break;
                }

                $row[] = $genero;

                $row[] = "Cabeza de familia";
                $tipo = "";
                switch ($aspirante['Candidate']['tipo']) {
                    case 2:
                        $tipo = "Campesino";
                        break;
                    case 4:
                        $tipo = "Desplazado";
                        break;
                    case 5:
                        $tipo = "Profesional";
                        break;
                    default:
                        $tipo = "Sin registro";
                        break;
                }
                $row[] = $tipo;
                $row[] = "";
                //Calificacion SISBEN
                $req_sisben = $this->Candidate->query('SELECT CandidateRequirement.id,CandidateRequirement.calificacion,CandidateRequirement.cal_subsanacion
                        FROM candidate_requirements CandidateRequirement LEFT JOIN candidates Candidate ON Candidate.id=CandidateRequirement.candidate_id
                        WHERE Candidate.id=' . $aspirante['Candidate']['id'] . " AND CandidateRequirement.requirement_id in(12,36)
                        ORDER BY CandidateRequirement.claim_id ASC");
                $sisben = "Cumple";
                foreach ($req_sisben as $req) {

                    if ($req['CandidateRequirement']['calificacion'] != 1) {


                        if ($req['CandidateRequirement']['cal_subsanacion'] != 1) {
                            $sisben = "No cumple";
                        } else {
                            $sisben = "Cumple";
                        }
                    } else {
                        $sisben = "Cumple";
                    }

                    if ($reclamacion = $this->Candidate->query("SELECT CandidateRequirementClaim.calificacion FROM candidate_requirement_claims CandidateRequirementClaim WHERE CandidateRequirementClaim.candidate_requirement_id=" . $req['CandidateRequirement']['id'] . " ORDER BY CandidateRequirementClaim.id DESC LIMIT 1")) {

                        if ($reclamacion[0]['CandidateRequirementClaim']['calificacion'] != 'Desvirtúa la causal') {
                            $sisben = "No cumple";
                        }
                    }
                }
                if ($aspirante['Candidate']['tipo'] != 4) {
                    $row[] = $sisben;
                } else {
                    $row[] = "N/A";
                }


                //Calificacion DAS
                $req_das = $this->Candidate->query('SELECT CandidateRequirement.calificacion,CandidateRequirement.cal_subsanacion
                        FROM candidate_requirements CandidateRequirement 
                        LEFT JOIN candidates Candidate 
                        ON Candidate.id=CandidateRequirement.candidate_id 
                        WHERE Candidate.id=' . $aspirante['Candidate']['id'] . " 
                        AND CandidateRequirement.requirement_id in(14,29,31,32,33,34) ORDER BY CandidateRequirement.claim_id ASC");
                $das = "Cumple";
                foreach ($req_das as $req) {
                    if ($req['CandidateRequirement']['calificacion'] != 1) {
                        if ($req['CandidateRequirement']['cal_subsanacion'] != 1) {
                            $das = "No cumple";
                        } else {
                            $das = "Cumple";
                        }
                    } else {
                        $das = "Cumple";
                    }
                }
                $row[] = $das;
                //Calificacion sipod
                $req_sipod = $this->Candidate->query('SELECT CandidateRequirement.calificacion,CandidateRequirement.cal_subsanacion FROM candidate_requirements CandidateRequirement LEFT JOIN candidates Candidate ON Candidate.id=CandidateRequirement.candidate_id WHERE Candidate.id=' . $aspirante['Candidate']['id'] . " AND CandidateRequirement.requirement_id in(16,22) ORDER BY CandidateRequirement.claim_id ASC");
                $sipod = "Cumple";
                foreach ($req_sipod as $req) {
                    if ($req['CandidateRequirement']['calificacion'] != 1) {
                        if ($req['CandidateRequirement']['cal_subsanacion'] != 1) {
                            $das = "No cumple";
                        } else {
                            $sipod = "Cumple";
                        }
                    } else {
                        $sipod = "Cumple";
                    }
                }

                if ($aspirante['Candidate']['tipo'] == 4) {
                    $row[] = $sipod;
                } else {
                    $row[] = "N/A";
                }

                //Calificacion incore
                $req_incora = $this->Candidate->query('SELECT CandidateRequirement.calificacion,CandidateRequirement.cal_subsanacion FROM candidate_requirements CandidateRequirement LEFT JOIN candidates Candidate ON Candidate.id=CandidateRequirement.candidate_id WHERE Candidate.id=' . $aspirante['Candidate']['id'] . " AND CandidateRequirement.requirement_id in(6,13,56,57,60) ORDER BY CandidateRequirement.claim_id ASC");
                $incora = "Cumple";
                foreach ($req_incora as $req) {
                    if ($req['CandidateRequirement']['calificacion'] != 1) {
                        if ($req['CandidateRequirement']['cal_subsanacion'] != 1) {
                            $incora = "No cumple";
                        } else {
                            $incora = "Cumple";
                        }
                    } else {
                        $incora = "Cumple";
                    }
                }

                if ($aspirante['Candidate']['tipo'] != 4) {
                    $row[] = $incora;
                } else {
                    $row[] = "N/A";
                }
                fputcsv($csv_file, $row, ';', '"');
                //DESDE ACÁ SE CARGAN LAS LOS DATOS CORRESPONDIENTES A LOS CONYUGUES  


                if ($conyuge = $this->Candidate->find('first', array('recursive' => -1, 'conditions' => array('Candidate.candidate_id' => $aspirante['Candidate']['id']), 'fields' => array('Candidate.id', 'Candidate.candidate_id', "Candidate.tipo_ident", "Candidate.nro_ident", "Candidate.1er_apellido", "Candidate.2do_apellido", "Candidate.1er_nombre", "Candidate.genero", "Candidate.tipo", "Candidate.clase", "Candidate.duplicado", "Candidate.proyect_id", "Candidate.2do_nombre")))) {
                    $row = array();
                    $row[] = "SIT-2011-01";
                    $row[] = $Proyecto['Proyect']['codigo'];
                    $row[] = $aspirante['Candidate']['id'];
                    $row[] = $tipo_iden;
                    $row[] = $aspirante['Candidate']['nro_ident'];

                    $tipo_iden = "";
                    switch ($conyuge['Candidate']['tipo_ident']) {
                        case 1:
                            $tipo_iden = "C.C";
                            break;
                        case 2:
                            $tipo_iden = "T.I";
                            break;

                        default:
                            $tipo_iden = "Sin registro";
                            break;
                    }

                    $row[] = $tipo_iden;
                    $row[] = $conyuge['Candidate']['nro_ident'];
                    $row[] = $conyuge['Candidate']['1er_apellido'];
                    $row[] = $conyuge['Candidate']['2do_apellido'];
                    $row[] = $conyuge['Candidate']['1er_nombre'] . " " . $conyuge['Candidate']['2do_nombre'];
                    $genero = "";

                    switch ($conyuge['Candidate']['genero']) {
                        case 1:
                            $genero = "Hombre";
                            break;
                        case 2:
                            $genero = "Mujer";
                            break;

                        default:
                            $genero = "Sin registro";
                            break;
                    }

                    $row[] = $genero;

                    $row[] = "Cónyuge";
                    $tipo = "";
                    switch ($conyuge['Candidate']['tipo']) {
                        case 6:
                            $tipo = "Campesino";
                            break;
                        case 7:
                            $tipo = "Desplazado";
                            break;
                        case 8:
                            $tipo = "Profesional";
                            break;
                        default:
                            $tipo = "Sin registro";
                            break;
                    }
                    $row[] = $tipo;
                    $row[] = "";
                    //Calificacion SISBEN
                    $req_sisben = $this->Candidate->query('SELECT CandidateRequirement.calificacion,CandidateRequirement.cal_subsanacion FROM candidate_requirements CandidateRequirement LEFT JOIN candidates Candidate ON Candidate.id=CandidateRequirement.candidate_id WHERE Candidate.id=' . $conyuge['Candidate']['id'] . " AND CandidateRequirement.requirement_id in(12,36) ORDER BY CandidateRequirement.claim_id ASC");
                    $sisben = "Cumple";
                    foreach ($req_sisben as $req) {
                        if ($req['CandidateRequirement']['calificacion'] != 1) {
                            if ($req['CandidateRequirement']['cal_subsanacion'] != 1) {
                                $sisben = "No cumple";
                            } else {
                                $sisben = "Cumple";
                            }
                        } else {
                            $sisben = "Cumple";
                        }
                    }
                    if ($conyuge['Candidate']['tipo'] != 7) {
                        $row[] = $sisben;
                    } else {
                        $row[] = "N/A";
                    }


                    //Calificacion DAS
                    $req_das = $this->Candidate->query('SELECT CandidateRequirement.calificacion,CandidateRequirement.cal_subsanacion  FROM candidate_requirements CandidateRequirement LEFT JOIN candidates Candidate ON Candidate.id=CandidateRequirement.candidate_id WHERE Candidate.id=' . $conyuge['Candidate']['id'] . " AND CandidateRequirement.requirement_id in(14,29,31,32,33,34) ORDER BY CandidateRequirement.claim_id ASC");
                    $das = "Cumple";
                    foreach ($req_das as $req) {
                        if ($req['CandidateRequirement']['calificacion'] != 1) {
                            if ($req['CandidateRequirement']['cal_subsanacion'] != 1) {
                                $das = "No cumple";
                            } else {
                                $das = "Cumple";
                            }
                        } else {
                            $das = "Cumple";
                        }
                    }
                    $row[] = $das;
                    //Calificacion sipod
                    $req_sipod = $this->Candidate->query('SELECT CandidateRequirement.calificacion,CandidateRequirement.cal_subsanacion FROM candidate_requirements CandidateRequirement LEFT JOIN candidates Candidate ON Candidate.id=CandidateRequirement.candidate_id WHERE Candidate.id=' . $conyuge['Candidate']['id'] . " AND CandidateRequirement.requirement_id in(16,22) ORDER BY CandidateRequirement.claim_id ASC");
                    $sipod = "Cumple";
                    foreach ($req_sipod as $req) {
                        if ($req['CandidateRequirement']['calificacion'] != 1) {
                            if ($req['CandidateRequirement']['cal_subsanacion'] != 1) {
                                $das = "No cumple";
                            } else {
                                $sipod = "Cumple";
                            }
                        } else {
                            $sipod = "Cumple";
                        }
                    }

                    if ($conyuge['Candidate']['tipo'] == 7) {
                        $row[] = $sipod;
                    } else {
                        $row[] = "N/A";
                    }

                    //Calificacion incore
                    $req_incora = $this->Candidate->query('SELECT CandidateRequirement.calificacion,CandidateRequirement.cal_subsanacion FROM candidate_requirements CandidateRequirement LEFT JOIN candidates Candidate ON Candidate.id=CandidateRequirement.candidate_id WHERE Candidate.id=' . $conyuge['Candidate']['id'] . " AND CandidateRequirement.requirement_id in(6,13,56,57,60) ORDER BY CandidateRequirement.claim_id ASC");
                    $incora = "Cumple";
                    foreach ($req_incora as $req) {
                        if ($req['CandidateRequirement']['calificacion'] != 1) {
                            if ($req['CandidateRequirement']['cal_subsanacion'] != 1) {
                                $incora = "No cumple";
                            } else {
                                $incora = "Cumple";
                            }
                        } else {
                            $incora = "Cumple";
                        }
                    }

                    if ($conyuge['Candidate']['tipo'] != 7) {
                        $row[] = $incora;
                    } else {
                        $row[] = "N/A";
                    }
                    fputcsv($csv_file, $row, ';', '"');
                }

                //INSRETAMOS LOS FAMILIARES CORESPONDIENTES
                $familiares = $this->Candidate->Relative->find('all', array('recursive' => -1, 'fields' => array('Relative.id', 'Relative.tipo_ident', 'Relative.nro_ident', 'Relative.nombres', 'Relative.1er_apellido', 'Relative.2do_apellido', 'Relative.genero', 'Relative.parentesco'), 'conditions' => array('Relative.candidate_id' => $aspirante['Candidate']['id'])));
                foreach ($familiares as $familiar) {
                    $row = array();
                    $row[] = "SIT-2011-01";
                    $row[] = $Proyecto['Proyect']['codigo'];
                    $row[] = $aspirante['Candidate']['id'];
                    $row[] = $tipo_iden;
                    $row[] = $aspirante['Candidate']['nro_ident'];

                    $tipo_iden = "";
                    switch ($familiar['Relative']['tipo_ident']) {
                        case 1:
                            $tipo_iden = "C.C";
                            break;
                        case 2:
                            $tipo_iden = "T.I";
                            break;
                        case 3:
                            $tipo_iden = "REGISTRO CIVIL";
                            break;
                        case 4:
                            $tipo_iden = "NUI";
                            break;

                        default:
                            $tipo_iden = "Sin registro";
                            break;
                    }

                    $row[] = $tipo_iden;
                    $row[] = $familiar['Relative']['nro_ident'];
                    $row[] = $familiar['Relative']['1er_apellido'];
                    $row[] = $familiar['Relative']['2do_apellido'];
                    $row[] = $familiar['Relative']['nombres'];
                    $genero = "";

                    switch ($familiar['Relative']['genero']) {
                        case 1:
                            $genero = "Hombre";
                            break;
                        case 2:
                            $genero = "Mujer";
                            break;

                        default:
                            $genero = "Sin registro";
                            break;
                    }

                    $row[] = $genero;

                    $parentesco = "";
                    switch ($familiar['Relative']['parentesco']) {
                        case 1:
                            $parentesco = "Hijo(a)";
                            break;
                        case 2:
                            $parentesco = "Hijastroa()";
                            break;
                        case 3:
                            $parentesco = "Padre";
                            break;
                        case 4:
                            $parentesco = "Madre";
                            break;
                        case 5:
                            $parentesco = "Abuelo(a)";
                            break;
                        case 6:
                            $parentesco = "Nieto(a)";
                            break;
                        case 7:
                            $parentesco = "Primo(a)";
                            break;
                        case 8:
                            $parentesco = "Sobrino(a)";
                            break;
                        case 9:
                            $parentesco = "Otro";
                            break;
                        default:
                            $parentesco = "Sin registro";
                            break;
                    }

                    $row[] = $parentesco;
                    $tipo = "";
                    switch ($aspirante['Candidate']['tipo']) {
                        case 2:
                            $tipo = "Campesino";
                            break;
                        case 4:
                            $tipo = "Desplazado";
                            break;
                        case 5:
                            $tipo = "Profesional";
                            break;
                        default:
                            $tipo = "Sin registro";
                            break;
                    }
                    $row[] = $tipo;
                    $row[] = "N/A";
                    $row[] = "N/A";
                    $row[] = "N/A";
                    $row[] = "N/A";
                    $row[] = "N/A";
                    fputcsv($csv_file, $row, ';', '"');
                }
            }
        }


        fclose($csv_file);
    }

}

?>
